﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;

namespace YJKRevitFamilyDataBase
{
	public class SqlServerCategoryTable : ICategoryTable
	{
		public SqlServerCategoryTable(FamilyLibDatabase db)
		{
			this.mDatabase = db;
		}

		public bool Add(DbCategory category, List<DbParamDefinition> cpdl)
		{
			byte[] value = null;
			if (category.Image != null)
			{
				MemoryStream memoryStream = new MemoryStream();
				category.Image.Save(memoryStream, ImageFormat.Jpeg);
				value = memoryStream.ToArray();
			}
			bool result;
			try
			{
				using (SqlCommand sqlCommand = ((SqlConnection)this.mDatabase.Connection).CreateCommand())
				{
					int num;
					if (category.Limit)
					{
						num = 1;
					}
					else
					{
						num = 0;
					}
					sqlCommand.CommandText = string.Format("insert into [CategoryTable] ([CategoryName],[ParentId],[Limit],[GUID],[KeyName],[IMG]) values ('{0}','{1}','{2}','{3}','{4}',{5})", new object[]
					{
						category.Name,
						category.ParentId.ToString(),
						num,
						category.Guid.ToString(),
						category.KeyName,
						"@img"
					});
					SqlParameter sqlParameter = new SqlParameter("@img", DbType.Binary);
					sqlParameter.Value = value;
					sqlCommand.Parameters.Add(sqlParameter);
					sqlCommand.ExecuteNonQuery();
					category.SetDatabase(this.mDatabase);
					this.mDatabase.CategoryParamTable.Add(category, cpdl);
					result = true;
				}
			}
			catch (Exception ex)
			{
				throw new Exception("添加分类出错！\n" + ex.Message);
			}
			return result;
		}

		public bool Add(Parent parent, DbCategory category)
		{
			bool result;
			try
			{
				using (SqlCommand sqlCommand = ((SqlConnection)this.mDatabase.Connection).CreateCommand())
				{
					int num;
					if (category.Limit)
					{
						num = 1;
					}
					else
					{
						num = 0;
					}
					sqlCommand.CommandText = string.Format("insert into [CategoryTable] ([CategoryName],[ParentId],[Limit],[GUID],[KeyName],[IMG]) values ('{0}','{1}','{2}','{3}','{4}',{5})", new object[]
					{
						category.Name,
						parent.Guid.ToString(),
						num,
						category.Guid.ToString(),
						category.KeyName,
						"@img"
					});
					SqlParameter sqlParameter = new SqlParameter("@img", DbType.Byte);
					if (category.Image != null)
					{
						MemoryStream memoryStream = new MemoryStream();
						category.Image.Save(memoryStream, ImageFormat.Jpeg);
						byte[] value = memoryStream.ToArray();
						sqlParameter.Value = value;
					}
					sqlCommand.Parameters.Add(sqlParameter);
					sqlCommand.ExecuteNonQuery();
					category.SetParentId(parent.Guid);
					category.SetDatabase(this.mDatabase);
					this.mDatabase.CategoryParamTable.Add(category, category.ParamDefinitionList);
					result = true;
				}
			}
			catch (Exception ex)
			{
				throw new Exception("添加分类出错！\n" + ex.Message);
			}
			return result;
		}

		public bool Remove(DbCategory category)
		{
			return this.mDatabase.ExecuteCommand(string.Format("delete  from CategoryTable where [GUID]='{0}' and [ParentId]='{1}'", category.Guid.ToString(), category.ParentId));
		}

		public bool Update(DbCategory category)
		{
			bool result;
			try
			{
				int num;
				if (category.Limit)
				{
					num = 1;
				}
				else
				{
					num = 0;
				}
				using (SqlCommand sqlCommand = ((SqlConnection)this.mDatabase.Connection).CreateCommand())
				{
					sqlCommand.CommandText = string.Format("update [CategoryTable] set [CategoryName]='{0}',[ParentId]='{1}',[Limit]='{2}',[Version]='{3}',[Description]='{4}',[IMG]={5},[KeyName]='{6}' where [GUID]='{7}'", new object[]
					{
						category.Name,
						category.ParentId.ToString(),
						num,
						category.Version,
						category.Description,
						"@img",
						category.KeyName,
						category.Guid.ToString()
					});
					SqlParameter sqlParameter = new SqlParameter("@img", DbType.Binary);
					if (category.Image != null)
					{
						MemoryStream memoryStream = new MemoryStream();
						category.Image.Save(memoryStream, ImageFormat.Jpeg);
						byte[] value = memoryStream.ToArray();
						sqlParameter.Value = value;
					}
					sqlCommand.Parameters.Add(sqlParameter);
					if (sqlCommand.ExecuteNonQuery() != -1)
					{
						result = true;
					}
					else
					{
						result = false;
					}
				}
			}
			catch (Exception ex)
			{
				throw new Exception("更新分类出错！\n" + ex.Message);
			}
			return result;
		}

		public DbCategory this[Guid id]
		{
			get
			{
				DataTable dataTable = this.mDatabase.ExecQuery(string.Format("select [Limit],[CategoryName],[ParentId],[GUID],[IMG],[Description],[Version],[KeyName] from [CategoryTable] where [GUID]='{0}'", id.ToString()));
				if (dataTable != null && dataTable.Rows.Count > 0)
				{
					DbCategory dbCategory = new DbCategory(dataTable.Rows[0][1].ToString().Trim(), new Guid(dataTable.Rows[0][2].ToString()));
					byte[] array = dataTable.Rows[0][4] as byte[];
					Image image;
					if (array != null)
					{
						image = new Bitmap(new MemoryStream(array));
					}
					else
					{
						image = new Bitmap(200, 200);
					}
					dbCategory.Image = image;
					dbCategory.SetId(new Guid(dataTable.Rows[0][3].ToString()));
					dbCategory.Limit = Convert.ToBoolean(dataTable.Rows[0][0]);
					dbCategory.Description = dataTable.Rows[0]["Description"].ToString();
					dbCategory.Version = dataTable.Rows[0]["Version"].ToString();
					dbCategory.SetDatabase(this.mDatabase);
					dbCategory.KeyName = dataTable.Rows[0]["KeyName"].ToString();
					return dbCategory;
				}
				return null;
			}
		}

		public DbCategory this[string name]
		{
			get
			{
				DbCategory dbCategory = null;
				DataTable dataTable = this.mDatabase.ExecQuery(string.Format("select [Limit],[CategoryName],[ParentId],[GUID],[IMG],[Description],[Version],[KeyName]  from [CategoryTable] where [CategoryName]='{0}'", name));
				if (dataTable != null && dataTable.Rows.Count > 0)
				{
					dbCategory = new DbCategory(dataTable.Rows[0][1].ToString().Trim(), new Guid(dataTable.Rows[0][2].ToString()));
					byte[] array = dataTable.Rows[0][4] as byte[];
					Image image;
					if (array != null)
					{
						image = new Bitmap(new MemoryStream(array));
					}
					else
					{
						image = new Bitmap(200, 200);
					}
					dbCategory.Image = image;
					dbCategory.SetId(new Guid(dataTable.Rows[0][3].ToString()));
					dbCategory.Limit = Convert.ToBoolean(dataTable.Rows[0][0]);
					dbCategory.Description = dataTable.Rows[0]["Description"].ToString();
					dbCategory.Version = dataTable.Rows[0]["Version"].ToString();
					dbCategory.KeyName = dataTable.Rows[0]["KeyName"].ToString();
					dbCategory.SetDatabase(this.mDatabase);
				}
				return dbCategory;
			}
		}

		public DbCategory this[string name, bool parentIsRoot]
		{
			get
			{
				DbCategory dbCategory = null;
				DataTable dataTable = this.mDatabase.ExecQuery(string.Format("select [Limit],[CategoryName],[ParentId],[GUID],[IMG],[Description],[Version],[KeyName]  from [CategoryTable] where [CategoryName]='{0}'", name));
				if (dataTable != null && dataTable.Rows.Count > 0)
				{
					dbCategory = new DbCategory(dataTable.Rows[0][1].ToString().Trim(), new Guid(dataTable.Rows[0][2].ToString()));
					byte[] array = dataTable.Rows[0][4] as byte[];
					Image image;
					if (array != null)
					{
						image = new Bitmap(new MemoryStream(array));
					}
					else
					{
						image = new Bitmap(200, 200);
					}
					dbCategory.Image = image;
					dbCategory.SetId(new Guid(dataTable.Rows[0][3].ToString()));
					dbCategory.Limit = Convert.ToBoolean(dataTable.Rows[0][0]);
					dbCategory.Description = dataTable.Rows[0]["Description"].ToString();
					dbCategory.Version = dataTable.Rows[0]["Version"].ToString();
					dbCategory.KeyName = dataTable.Rows[0]["KeyName"].ToString();
					dbCategory.SetDatabase(this.mDatabase);
				}
				return dbCategory;
			}
		}

		public List<DbCategory> this[Parent parent]
		{
			get
			{
				List<DbCategory> list = new List<DbCategory>();
				DataTable dataTable = this.mDatabase.ExecQuery(string.Format("select [CategoryName],[Limit],[ParentId],[GUID],[IMG],[Description],[Version],[KeyName]  from [CategoryTable] where [ParentId]='{0}'", parent.Guid.ToString()));
				if (dataTable != null && dataTable.Rows.Count > 0)
				{
					for (int i = 0; i < dataTable.Rows.Count; i++)
					{
						DbCategory dbCategory = new DbCategory(dataTable.Rows[i][0].ToString().Trim(), new Guid(dataTable.Rows[i][2].ToString()));
						byte[] array = dataTable.Rows[i][4] as byte[];
						Image image;
						if (array != null)
						{
							image = new Bitmap(new MemoryStream(array));
						}
						else
						{
							image = new Bitmap(200, 200);
						}
						dbCategory.Image = image;
						dbCategory.SetId(new Guid(dataTable.Rows[i][3].ToString()));
						dbCategory.Limit = Convert.ToBoolean(dataTable.Rows[i][1]);
						dbCategory.Description = dataTable.Rows[i]["Description"].ToString();
						dbCategory.Version = dataTable.Rows[i]["Version"].ToString();
						dbCategory.KeyName = dataTable.Rows[i]["KeyName"].ToString();
						dbCategory.SetDatabase(this.mDatabase);
						list.Add(dbCategory);
					}
				}
				return list;
			}
		}

		public List<DbCategory> GetAll()
		{
			List<DbCategory> list = new List<DbCategory>();
			DataTable dataTable = this.mDatabase.ExecQuery("select [CategoryName],[Limit],[ParentId],[GUID],[IMG],[Description],[Version],[KeyName]  from [CategoryTable]");
			if (dataTable != null && dataTable.Rows.Count > 0)
			{
				for (int i = 0; i < dataTable.Rows.Count; i++)
				{
					DbCategory dbCategory = new DbCategory(dataTable.Rows[i][0].ToString().Trim(), new Guid(dataTable.Rows[i][2].ToString()));
					byte[] array = dataTable.Rows[i][4] as byte[];
					Image image;
					if (array != null)
					{
						image = new Bitmap(new MemoryStream(array));
					}
					else
					{
						image = new Bitmap(200, 200);
					}
					dbCategory.Image = image;
					dbCategory.SetId(new Guid(dataTable.Rows[i][3].ToString()));
					dbCategory.Limit = Convert.ToBoolean(dataTable.Rows[i][1]);
					dbCategory.Description = dataTable.Rows[i]["Description"].ToString().Trim();
					dbCategory.Version = dataTable.Rows[i]["Version"].ToString();
					dbCategory.KeyName = dataTable.Rows[i]["KeyName"].ToString().Trim();
					dbCategory.SetDatabase(this.mDatabase);
					list.Add(dbCategory);
				}
			}
			return list;
		}

		private FamilyLibDatabase mDatabase;
	}
}
